﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_DAO.db;
using Cashier_Model;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace Cashier_DAO
{
    public class BillShipmentGoodsDAO: DAOFactory
    {
        /// <summary>
        /// 查询出货单商品
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public BillShipmentGoods GetBillShipmentGoods(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            BillShipmentGoods result = null;
            try
            {
                StringBuilder sb = new StringBuilder("select g.*,s.member_no,s.shipment_date from bill_shipment_goods g,bill_shipment s");
                sb.Append(" where g.shipment_no=s.shipment_no");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, shipmentGoods);

                if (paramtable.Keys.Count > 0)
                {
                    DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                    this.AddCommandParamters(cmd, paramtable);
                    DataTable dt = new DataTable("bill_shipment_goods");
                    DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                    da.Fill(dt);

                    if (dt != null && dt.Rows.Count == 1)
                    {
                        DataRow row = dt.Rows[0];
                        result = new BillShipmentGoods();
                        this.DataRowToModel(row, result);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 查询出货单商品列表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public DataTable GetBillShipmentGoodsTable(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder("select g.*,s.member_no,s.shipment_date from bill_shipment_goods g,bill_shipment s");
                sb.Append(" where g.shipment_no=s.shipment_no");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, shipmentGoods);

                if (!String.IsNullOrEmpty(shipmentGoods.StartDate) && !String.IsNullOrEmpty(shipmentGoods.EndDate))
                {
                    sb.Append(" and (date(s.shipment_date)>=@startdate and date(s.shipment_date)<=@enddate)");
                    paramtable.Add("startdate", shipmentGoods.StartDate);
                    paramtable.Add("enddate", shipmentGoods.EndDate);
                }

                this.GenerateOrderAndPageSQL(sb, paramtable, shipmentGoods);

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_shipment_goods");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 查询出货单商品记录数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public long GetBillShipmentGoodsRecord(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            long record = 0;
            try
            {
                StringBuilder sb = new StringBuilder("select count(g.id) as c from bill_shipment_goods g,bill_shipment s");
                sb.Append(" where g.shipment_no=s.shipment_no");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, shipmentGoods);

                if (!String.IsNullOrEmpty(shipmentGoods.StartDate) && !String.IsNullOrEmpty(shipmentGoods.EndDate))
                {
                    sb.Append(" and (date(s.shipment_date)>=@startdate and date(s.shipment_date)<=@enddate)");
                    paramtable.Add("startdate", shipmentGoods.StartDate);
                    paramtable.Add("enddate", shipmentGoods.EndDate);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                Object obj = cmd.ExecuteScalar();
                if (obj != null)
                    record = (long)obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return record;
        }

        /// <summary>
        /// 插入出货单商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public long InsertBillShipmentGoods(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            long id = 0;
            try
            {
                id = (long)this.Insert(conn, shipmentGoods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// 更新出货单商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public int UpdateBillShipmentGoods(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            int flag = 0;
            try
            {
                flag = this.Update(conn, shipmentGoods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 删除出货单商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="shipmentGoods"></param>
        /// <returns></returns>
        public int DeleteBillShipmentGoods(DbConnection conn, BillShipmentGoods shipmentGoods)
        {
            int count = 0;
            try
            {
                count = this.Delete(conn, shipmentGoods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }
    }
}
